/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package conceitos.DAO;

import conceitos.modelo.entidades.Conta;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;

/**
 *
 * @author jeferson
 */
public class ContaDAO {
    private Connection conexao;

    public ContaDAO() {
        conexao = Conexao.getInstance();
    }

    // new id
    private Integer newIdConta() throws SQLException {
        String sql = "select nextval('conta_idconta_seq') as new";
        ResultSet rs = conexao.createStatement().executeQuery(sql);
        if (!rs.next()) {
            rs.close();
            return null;
        }
        return rs.getInt("new");
    }
    
    // insert
    public boolean insert(Conta conta){
        String sql = "insert into conta (idconta, idaluno, login, statusconta) values(?,?,?,?)";
        try {
            conta.setIdConta(newIdConta());
            PreparedStatement ps = conexao.prepareStatement(sql);
            ps.setInt(1, conta.getIdConta());
            ps.setInt(2, conta.getIdAluno());
            ps.setString(3, conta.getLogin());
            ps.setInt(4, conta.getStatusConta());
            int retorno = ps.executeUpdate();
            conexao.commit();
            if (retorno > 0) {
                return true;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            JOptionPane.showMessageDialog(null, "Erro: " + ex.getMessage());
        }

        return false;
    }

    // update
    public boolean update(Conta conta) {
        String sql = "update conta set login = ?, statusconta = ? where idconta = "+conta.getIdConta();
        try {
            PreparedStatement ps = conexao.prepareStatement(sql);
            ps.setString(1, conta.getLogin());
            ps.setInt(2, conta.getStatusConta());
            int retorno = ps.executeUpdate();
            conexao.commit();
            if (retorno > 0) {            
                return true;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            JOptionPane.showMessageDialog(null, "Erro: " + ex.getMessage());
        }
        return false;
    }

    // delete
    public boolean delete(Integer idConta) {
        String sql = "delete from conta where idConta = ?";
        try {
            PreparedStatement ps = conexao.prepareStatement(sql);
            ps.setObject(1, idConta);
            if (ps.executeUpdate() > 0) {
                return true;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            JOptionPane.showMessageDialog(null, "Erro: " + ex.getMessage());
        }
        return false;
    }
    
    public Conta select(String filtro) {
        if (filtro == null || filtro.trim().isEmpty()) {
            filtro = "1 = 1";
        }
        String sql = "select * from conta where " + filtro;
        try {
            Conta conta = null;
            ResultSet rs = conexao.createStatement().executeQuery(sql);
            while (rs.next()) {
                conta = new Conta();
                if (rs.isLast()) {
                    conta.setIdConta(rs.getInt("idconta"));
                    conta.setIdAluno(rs.getInt("idaluno"));
                    conta.setLogin(rs.getString("login"));
                    conta.setStatusConta(rs.getInt("statusconta"));
                }
            }
            rs.close();
            return conta;
        } catch (SQLException ex) {
            ex.printStackTrace();
            JOptionPane.showMessageDialog(null, "Erro: " + ex.getMessage());
        }
        return null;
    }

    // select
    public List<Conta> selectAll(String filtro) {
        if (filtro == null || filtro.trim().isEmpty()) {
            filtro = "1 = 1";
        }
        String sql = "select * from conta where " + filtro;
        try {
            ResultSet rs = conexao.createStatement().executeQuery(sql);
            List<Conta> contas = new ArrayList<Conta>();
            while (rs.next()) {
                Conta conta = new Conta();
                conta.setIdConta(rs.getInt("idconta"));
                conta.setIdAluno(rs.getInt("idaluno"));
                conta.setLogin(rs.getString("login"));
                conta.setStatusConta(rs.getInt("statusconta"));
                contas.add(conta);
            }
            rs.close();
            return contas;
        } catch (SQLException ex) {
            ex.printStackTrace();
            JOptionPane.showMessageDialog(null, "Erro: " + ex.getMessage());
        }
        return null;
    }

    public String getSqlPesquisa() {
        String sql = "select idconta as \"IDCONTA\",      "
                   + "       idaluno as \"IDALUNO\",  "
                   + "       login as \"LOGIN\",  "
                   + "       statusconta as \"STATUSCONTA\"       "
                   + "  from conta             "
                   + " where upper(login || '%' || senha) like upper(?)"
                   + " order by login";
        return sql;
    }
}
